{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "734fec1e",
   "metadata": {},
   "source": [
    "<h1 align=\"center\">实验1 数字营商环境指标数据采集与转换</h1>\n",
    "\n",
    "<a id=\"ref1\"></a>\n",
    "# 实验目的\n",
    "<div class=\"alert alert-block alert-success\" style=\"margin-top: 20px\">\n",
    "本实验旨在帮助学生掌握数字营商环境数据的采集、转换和处理方法。通过本实验，学生将学习如何从世界银行官网获取指定国家和时间序列的相关指标数据，并进行数据格式转换和补齐操作，最终构建全面的数字营商环境数据集，为后续的分析奠定基础。\n",
    "</div>\n",
    "\n",
    "<a id=\"ref2\"></a>\n",
    "# 实验要求\n",
    "<div class=\"alert alert-block alert-warning\" style=\"margin-top: 20px\">\n",
    "1. <b>数据采集：</b><br>\n",
    "   - 学生需熟练掌握世界银行官网的数据检索功能，能够按指定指标、国家和时间序列下载数据，并进行相应的布局调整。<br>\n",
    "2. <b>数据转换：</b><br>\n",
    "   - 学生需学习并应用Python程序将下载的指标数据进行行列转换，确保数据格式符合后续分析要求。<br>\n",
    "3. <b>数据补齐：</b><br>\n",
    "   - 学生需掌握补齐缺失数据的方法，通过与补齐模板数据集的左连接操作，确保所有国家和年份的数据完整性。<br>\n",
    "4. <b>数据合并：</b><br>\n",
    "   - 学生需能够将处理后的各个指标数据集合并为完整的数字营商环境数据集，并确保数据的一致性和准确性。<br>\n",
    "5. <b>数据核对：</b><br>\n",
    "   - 学生需对合并后的数据集进行随机抽查，确保数据转换和补齐的正确性，避免遗漏和错误。<br>\n",
    "</div>\n",
    "\n",
    "<a id=\"ref3\"></a>\n",
    "# 实验步骤\n",
    "<div class=\"alert alert-block alert-info\" style=\"margin-top: 20px\">\n",
    "<li><b>步骤1: 数据采集与布局调整：</b> 登录世界银行官网，选择目标指标、国家和时间序列，执行查询命令（Apply Change），并将Layout更改为Time(Row)、Series(Column)、Country(Column)。下载的数据文件命名为“P_Data_Extract_From_World_Development_Indicators.xlsx”，并及时更名为“指标名__Indicators.xlsx”。</li>\n",
    "\n",
    "<li><b>步骤2: 清理数据文件：</b> 打开下载的Excel文件，删除Data工作表中的多余信息（如Data from database、Last Updated）以保证数据清洁。</li>\n",
    "\n",
    "<li><b>步骤3: 数据行列转换：</b> 使用Python编写程序，对单个指标数据集进行行列转换。设定原始指标文件所在目录，批量处理所有指标文件，确保格式一致。</li>\n",
    "\n",
    "<li><b>步骤4: F系列指标单独转换：</b> 针对数据来源不同的F系列指标，单独编写Python程序实现行列转换，确保数据处理的一致性。</li>\n",
    "\n",
    "<li><b>步骤5: 补齐缺失数据：</b> 针对数字营商环境中缺失的国家和年份数据，使用预设的补齐模板数据集进行补齐操作。将转换后的指标数据集与模板数据集进行左连接，确保所有国家和年份数据都涵盖。</li>\n",
    "\n",
    "<li><b>步骤6: 合并所有指标数据：</b> 读取处理后的所有指标数据文件，并合并为一个完整的数字营商环境数据集“AllIndexData.xlsx”。</li>\n",
    "\n",
    "<li><b>步骤7: 数据核对与校验：</b> 打开合并后的数据集文件，随机抽查部分数据的完整性和一致性，确保数据与原始指标相符，无遗漏和错误。</li>\n",
    "</div>\n",
    "\n",
    "<a id=\"ref4\"></a>\n",
    "# 注意事项\n",
    "<div class=\"alert alert-block alert-danger\" style=\"margin-top: 20px\">\n",
    "<li>确保下载的数据布局正确，避免在后续处理中因布局错误导致的数据错位。</li>\n",
    "<li>清理Excel文件时注意删除多余信息，确保数据文件仅保留必要的数值内容。</li>\n",
    "<li>行列转换时，注意检查Python代码的逻辑，确保数据转换的准确性。</li>\n",
    "<li>补齐缺失数据时，补齐模板的数据要符合实际情况，避免不合理的填补。</li>\n",
    "<li>在合并数据文件后，务必仔细核查合并结果，确保所有数据都准确无误。</li>\n",
    "</div>\n",
    "\n",
    "<p>通过本实验，学生将掌握数字营商环境数据的采集、转换与处理技能，为后续的数字经济分析打下坚实的基础。</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38b7cf1d",
   "metadata": {},
   "source": [
    "# 单一指标数据转换示例\n",
    "- 要求指标文件中Data工作表除规范表格数据，没有多余的数据内容（需要先清除掉）\n",
    "- 原始指标数据重命名名称格式为：<span style=\"color:red\"><b>指标名+_indacator</b></span>\n",
    "- 下面代码中只是转换“D1”指标文件进行测试"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "dbb56483",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "转换前数据集如下：\n",
      "   Time Time Code  \\\n",
      "0  2009    YR2009   \n",
      "1  2010    YR2010   \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Albania [ALB]  \\\n",
      "0                                               41.2                                  \n",
      "1                                                 45                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Algeria [DZA]  \\\n",
      "0                                              11.23                                  \n",
      "1                                               12.5                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Austria [AUT]  \\\n",
      "0                                              73.45                                  \n",
      "1                                              75.17                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Bangladesh [BGD]  \\\n",
      "0                                                3.1                                     \n",
      "1                                                3.7                                     \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Belarus [BLR]  \\\n",
      "0                                              27.43                                  \n",
      "1                                               31.8                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Bhutan [BTN]  \\\n",
      "0                                               7.17                                 \n",
      "1                                               13.6                                 \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Bosnia and Herzegovina [BIH]  \\\n",
      "0                                              37.74                                                 \n",
      "1                                              42.75                                                 \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Bulgaria [BGR]  \\\n",
      "0                                                 45                                   \n",
      "1                                              46.23                                   \n",
      "\n",
      "   ...  \\\n",
      "0  ...   \n",
      "1  ...   \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Slovak Republic [SVK]  \\\n",
      "0                                                 70                                          \n",
      "1                                              75.71                                          \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Slovenia [SVN]  \\\n",
      "0                                                 64                                   \n",
      "1                                                 70                                   \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Solomon Islands [SLB]  \\\n",
      "0                                                  4                                          \n",
      "1                                                  5                                          \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - South Africa [ZAF]  \\\n",
      "0                                                 10                                       \n",
      "1                                                 24                                       \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Thailand [THA]  \\\n",
      "0                                               20.1                                   \n",
      "1                                               22.4                                   \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Turkiye [TUR]  \\\n",
      "0                                               36.4                                  \n",
      "1                                              39.82                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Ukraine [UKR]  \\\n",
      "0                                               17.9                                  \n",
      "1                                               23.3                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Uruguay [URY]  \\\n",
      "0                                               41.8                                  \n",
      "1                                               46.4                                  \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Uzbekistan [UZB]  \\\n",
      "0                                               11.9                                     \n",
      "1                                               15.9                                     \n",
      "\n",
      "  Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Viet Nam [VNM]  \n",
      "0                                              26.55                                  \n",
      "1                                              30.65                                  \n",
      "\n",
      "[2 rows x 57 columns]\n",
      "转换后数据集如下：\n",
      "      国名En   国名Ch    S1  Year\n",
      "0  Albania  阿尔巴尼亚  41.2  2009\n",
      "1  Albania  阿尔巴尼亚    45  2010\n",
      "2  Albania  阿尔巴尼亚    47  2011\n",
      "3  Albania  阿尔巴尼亚  49.4  2012\n",
      "4  Albania  阿尔巴尼亚  51.8  2013\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd  # 导入pandas库用于数据处理\n",
    "import re  # 导入正则表达式模块，用于字符串操作\n",
    "\n",
    "# 读取指标原始Excel文件\n",
    "input_directory = 'Data/1_营商数据集转换/Origin/'  # 指定指标原始文件所在目录\n",
    "output_dirctory = 'Data/1_营商数据集转换/afterProcess/'\n",
    "file_name = \"S1_Indicators.xlsx\"\n",
    "# 使用字符串分割方法，获取文件名的前半部分，作为索引名称\n",
    "indexName = file_name.split(\"_Indicators\")[0]  \n",
    "# 从指定路径读取Excel文件的\"Data\"工作表，保存为DataFrame\n",
    "df = pd.read_excel(input_directory + '/' + file_name, sheet_name=\"Data\")\n",
    "\n",
    "# 打印转换前的数据行示例\n",
    "print(\"转换前数据集如下：\")\n",
    "print(df.head(2))  # 使用print()来输出\n",
    "\n",
    "# 初始化一个空列表，用于存储转换后的数据行\n",
    "rows = []\n",
    "\n",
    "# 读取包含国家列表及其对应中文名称的Excel文件\n",
    "country_list_df = pd.read_excel('Data/国家名称对照数据表.xlsx')\n",
    "# 创建一个字典，将国家简称映射到其英文和中文名称\n",
    "country_dict = {row['国家名Jc']: (row['国家名En'], row['国家名Ch']) for index, row in country_list_df.iterrows()}\n",
    "\n",
    "# 遍历原始DataFrame的每一列（除了'Time'列）\n",
    "for column in df.columns[1:]:\n",
    "    # 使用正则表达式从列名中提取方括号内的国家英文简称\n",
    "    match = re.search(r'\\[([A-Z]+)\\]$', column)  \n",
    "    if match:\n",
    "        country_code = match.group(1).strip()  # 获取国家英文简称并去除两端空格\n",
    "        if country_code in country_dict:\n",
    "            country_en, country_ch = country_dict[country_code]  # 从字典中获取国家英文和中文名称\n",
    "        else:\n",
    "            continue  # 如果字典中没有对应的国家简称，则跳过当前列\n",
    "    else:\n",
    "        continue  # 如果正则表达式未找到匹配项，则跳过当前列\n",
    "\n",
    "    # 为当前国家的每一年创建一个数据行\n",
    "    for index, row in df.iterrows():\n",
    "        # 将每一行的数据以字典形式存入rows列表\n",
    "        rows.append({\n",
    "            '国名En': country_en,\n",
    "            '国名Ch': country_ch,\n",
    "            indexName: row[column],  # 指标值\n",
    "            'Year': row['Time']  # 年份\n",
    "        })\n",
    "\n",
    "# 将收集到的行数据转换为DataFrame\n",
    "transformed_df = pd.DataFrame(rows, columns=['国名En', '国名Ch', indexName, 'Year'])\n",
    "\n",
    "# 打印转换后的数据行示例\n",
    "print(\"转换后数据集如下：\")\n",
    "print(transformed_df.head(5))  # 使用print()来正确输出\n",
    "\n",
    "# 将转换后的DataFrame保存到新的Excel文件，不包含索引列\n",
    "transformed_df.to_excel(output_dirctory + indexName + '_AfterProcess.xlsx', index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdae123c",
   "metadata": {},
   "source": [
    "正则表达式 `r'\\[([A-Z]+)\\]$'` 是用来匹配方括号 `[]` 中的大写字母组合，并且确保匹配的是字符串的末尾部分。具体解释如下：\n",
    "\n",
    "1. **r''**: 这是一个**原始字符串**标记。在 Python 中，前缀 `r` 告诉解释器这个字符串是原始字符串，意味着反斜杠 `\\` 不会被解释为转义字符。这对于正则表达式来说很有用，因为正则表达式中常常使用反斜杠来表示特殊模式。\n",
    "\n",
    "2. **\\\\[**: 匹配一个左方括号 `[`。因为方括号是正则表达式中的特殊字符，必须用反斜杠 `\\` 进行转义，`\\\\[` 才能匹配实际的左方括号。\n",
    "\n",
    "3. **([A-Z]+)**: 这部分是一个**捕获组**：\n",
    "   - **[A-Z]**: 匹配一个大写字母，`A` 到 `Z` 之间的任意字符。\n",
    "   - **+**: 表示匹配**一个或多个**前面的字符，即匹配一个或多个大写字母。\n",
    "   - 这个捕获组会提取方括号内的所有大写字母并返回作为结果。\n",
    "\n",
    "4. **\\\\]**: 匹配一个右方括号 `]`。同样地，方括号是特殊字符，所以需要用反斜杠进行转义。\n",
    "\n",
    "5. **$**: 匹配字符串的**末尾**。表示整个模式必须在字符串的末尾处出现，确保方括号及其内容位于字符串的结尾。\n",
    "\n",
    "**总结**\n",
    "这个正则表达式用于匹配以下形式的字符串：\n",
    "- **一个以方括号包围的大写字母组合，并且位于字符串的末尾**。\n",
    "\n",
    "例如：\n",
    "- 对于字符串 `\"Country [USA]\"`，这个正则表达式会匹配 `[USA]` 并捕获 `USA`。\n",
    "- 对于 `\"Country [CN]\"`，它会匹配 `[CN]` 并捕获 `CN`。\n",
    "- 不会匹配 `\"Country [USA] Extra\"`，因为方括号后面还有其他字符，不在字符串末尾。\n",
    "\n",
    "这个模式在处理文件列名或类似结构时，特别适用于提取国家代码或标识符等。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "adcac684",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BTN\n"
     ]
    }
   ],
   "source": [
    "match = re.search(r'\\[([A-Z]+)\\]$', \n",
    "                  'Individuals using the Internet (% of population) [IT.NET.USER.ZS] - Bhutan [BTN]')\n",
    "print( match.group(1).strip())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3dcb8a8",
   "metadata": {},
   "source": [
    "# 批量转换指标文件（除F系列外）\n",
    "\n",
    "1. **函数 `read_country_list(file_path)`：** 读取国家列表<span style=\"color:red\"><b>（world-development-indicators WDI数据库）</b></span>及其对应的中文名称，并将其转换为字典格式。国家名称字典根据不同数据库进行设置，下面是根据<span style=\"color:red\"><b>WDI数据库设置的国家字典数据</b></span>\n",
    "2. **函数 `process_file(file_path, country_dict, output_dir)`：** 处理单个Excel文件，进行数据转换并保存结果。如果成功，则输出成功信息；如果失败，则输出失败信息。\n",
    "3. **函数 `process_all_files(input_dir, country_list_path, output_dir)`：** 遍历指定目录下的所有Excel文件，并调用 `process_file` 函数处理每个文件。\n",
    "4. **主程序部分：** 定义输入目录、国家列表文件路径和输出目录，创建输出目录（如果不存在），并调用 `process_all_files` 函数处理所有文件。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6ada6620",
   "metadata": {
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "D1_Indicators.xlsx 读取并转换为 D1_AfterProcess.xlsx 成功\n",
      "D2_Indicators.xlsx 读取并转换为 D2_AfterProcess.xlsx 成功\n",
      "D3_Indicators.xlsx 读取并转换为 D3_AfterProcess.xlsx 成功\n",
      "F1_2_Indicators.xlsx 读取并转换为 F1_2_AfterProcess.xlsx 成功\n",
      "F1_Indicators.xlsx 读取并转换为 F1_AfterProcess.xlsx 成功\n",
      "L1_Indicators.xlsx 读取并转换为 L1_AfterProcess.xlsx 成功\n",
      "L2_Indicators.xlsx 读取并转换为 L2_AfterProcess.xlsx 成功\n",
      "L3_Indicators.xlsx 读取并转换为 L3_AfterProcess.xlsx 成功\n",
      "Q1_Indicators.xlsx 读取并转换为 Q1_AfterProcess.xlsx 成功\n",
      "Q2_2_Indicators.xlsx 读取并转换为 Q2_2_AfterProcess.xlsx 成功\n",
      "Q2_3_Indicators.xlsx 读取并转换为 Q2_3_AfterProcess.xlsx 成功\n",
      "Q2_Indicators.xlsx 读取并转换为 Q2_AfterProcess.xlsx 成功\n",
      "Q3_2_Indicators.xlsx 读取并转换为 Q3_2_AfterProcess.xlsx 成功\n",
      "Q3_2（national estimate)_Indicators.xlsx 读取并转换为 Q3_2（national estimate)_AfterProcess.xlsx 成功\n",
      "Q3_3_Indicators.xlsx 读取并转换为 Q3_3_AfterProcess.xlsx 成功\n",
      "Q3_Indicators.xlsx 读取并转换为 Q3_AfterProcess.xlsx 成功\n",
      "S1_Indicators.xlsx 读取并转换为 S1_AfterProcess.xlsx 成功\n",
      "S2_Indicators.xlsx 读取并转换为 S2_AfterProcess.xlsx 成功\n",
      "S3_Indicators.xlsx 读取并转换为 S3_AfterProcess.xlsx 成功\n",
      "S4_Indicators.xlsx 读取并转换为 S4_AfterProcess.xlsx 成功\n",
      "S5_Indicators.xlsx 读取并转换为 S5_AfterProcess.xlsx 成功\n",
      "T1_2_Indicators.xlsx 读取并转换为 T1_2_AfterProcess.xlsx 成功\n",
      "T1_Indicators.xlsx 读取并转换为 T1_AfterProcess.xlsx 成功\n",
      "T2_Indicators.xlsx 读取并转换为 T2_AfterProcess.xlsx 成功\n",
      "T3_2_Indicators.xlsx 读取并转换为 T3_2_AfterProcess.xlsx 成功\n",
      "T3_3_Indicators.xlsx 读取并转换为 T3_3_AfterProcess.xlsx 成功\n",
      "~$D1_Indicators.xlsx 读取并转换为 ~$D1_AfterProcess.xlsx 失败，请检查数据格式！\n",
      "错误信息: Excel file format cannot be determined, you must specify an engine manually.\n"
     ]
    }
   ],
   "source": [
    "import os  # 导入os模块，用于文件和目录操作\n",
    "import pandas as pd  # 导入pandas库用于数据处理\n",
    "import re  # 导入正则表达式模块，用于字符串操作\n",
    "\n",
    "def read_country_list(file_path):\n",
    "    \"\"\"\n",
    "    读取国家列表及其对应的中文、英文名称。\n",
    "    :param file_path: Excel文件路径，包含国家名称的对照表。\n",
    "    :return: 返回一个字典，键是国家名的简写，值是该国家的英文名和中文名。\n",
    "    \"\"\"\n",
    "    # 读取Excel文件，存储为DataFrame\n",
    "    country_list_df = pd.read_excel(file_path)\n",
    "    \n",
    "    # 将国家名简写与其英文名、中文名转换为字典格式\n",
    "    return {row['国家名Jc']: (row['国家名En'], row['国家名Ch']) for index, row in country_list_df.iterrows()}\n",
    "\n",
    "def process_file(file_path, country_dict, output_dir):\n",
    "    \"\"\"\n",
    "    处理单个Excel文件，将文件中的每个国家的数据按行展开，并保存为新文件。\n",
    "    :param file_path: 输入的Excel文件路径。\n",
    "    :param country_dict: 国家名称对照字典，包含简写、英文名、中文名。\n",
    "    :param output_dir: 输出文件的保存目录。\n",
    "    \"\"\"\n",
    "    try:\n",
    "        # 获取文件名（不包含路径）并从文件名中提取索引名称\n",
    "        file_name = os.path.basename(file_path)  # 从路径中获取文件名\n",
    "        index_name = file_name.split(\"_Indicators\")[0]  # 文件名中提取“指标”的名字部分\n",
    "        \n",
    "        # 读取Excel文件中的\"Data\"工作表\n",
    "        df = pd.read_excel(file_path, sheet_name=\"Data\")\n",
    "        \n",
    "        # 初始化一个空列表，用于存储转换后的每一行数据\n",
    "        rows = []\n",
    "        \n",
    "        # 遍历Excel文件中除'Time'列之外的所有列\n",
    "        for column in df.columns[1:]:\n",
    "            # 使用正则表达式从列名中提取国家的英文简称（格式如 \"[USA]\"）\n",
    "            match = re.search(r'\\[([A-Z]+)\\]$', column)\n",
    "            if match:\n",
    "                # 获取国家简称，并去掉空格\n",
    "                country_code = match.group(1).strip()\n",
    "                # 如果该国家简称在国家字典中，提取其中文名和英文名\n",
    "                if country_code in country_dict:\n",
    "                    country_en, country_ch = country_dict[country_code]\n",
    "                else:\n",
    "                    continue  # 如果找不到该国家简称，则跳过此列\n",
    "            else:\n",
    "                continue  # 如果列名不符合指定格式，则跳过此列\n",
    "\n",
    "            # 遍历该列的每一行数据\n",
    "            for index, row in df.iterrows():\n",
    "                # 为当前列中的每一年创建一个新的数据行\n",
    "                rows.append({\n",
    "                    '国名En': country_en,  # 国家英文名\n",
    "                    '国名Ch': country_ch,  # 国家中文名\n",
    "                    index_name: row[column],  # 指标值（对应的列数据）\n",
    "                    'Year': row['Time']  # 时间（年份）\n",
    "                })\n",
    "        \n",
    "        # 将收集到的行数据转换为一个新的DataFrame\n",
    "        transformed_df = pd.DataFrame(rows, columns=['国名En', '国名Ch', index_name, 'Year'])\n",
    "        \n",
    "        # 构建输出文件的保存路径\n",
    "        output_file_path = os.path.join(output_dir, index_name + '_AfterProcess.xlsx')\n",
    "        \n",
    "        # 将转换后的DataFrame保存为新的Excel文件，且不包含索引列\n",
    "        transformed_df.to_excel(output_file_path, index=False)\n",
    "        \n",
    "        # 输出成功提示信息\n",
    "        print(f\"{file_name} 读取并转换为 {index_name}_AfterProcess.xlsx 成功\")\n",
    "    \n",
    "    except Exception as e:\n",
    "        # 输出失败提示信息，并打印错误原因\n",
    "        print(f\"{file_name} 读取并转换为 {index_name}_AfterProcess.xlsx 失败，请检查数据格式！\")\n",
    "        print(f\"错误信息: {e}\")\n",
    "\n",
    "def process_all_files(input_dir, country_list_path, output_dir):\n",
    "    \"\"\"\n",
    "    遍历指定的目录，处理所有符合条件的Excel文件。\n",
    "    :param input_dir: 输入文件的目录路径。\n",
    "    :param country_list_path: 国家列表文件的路径。\n",
    "    :param output_dir: 输出文件的保存目录。\n",
    "    \"\"\"\n",
    "    # 读取国家列表文件，获取国家简称与英文名、中文名的对应关系\n",
    "    country_dict = read_country_list(country_list_path)\n",
    "    \n",
    "    # 遍历输入目录下的所有文件\n",
    "    for file_name in os.listdir(input_dir):\n",
    "        # 如果文件名以\"_Indicators.xlsx\"结尾，说明它是一个指标数据文件\n",
    "        if file_name.endswith(\"_Indicators.xlsx\"):\n",
    "            file_path = os.path.join(input_dir, file_name)  # 获取文件的完整路径\n",
    "            # 调用process_file函数处理当前文件\n",
    "            process_file(file_path, country_dict, output_dir)\n",
    "\n",
    "# 定义输入目录、国家列表文件路径和输出目录\n",
    "input_directory = 'Data/1_营商数据集转换/Origin'  # 包含原始数据集的目录\n",
    "country_list_file = 'Data/国家名称对照数据表.xlsx'  # 国家名称对照表文件\n",
    "output_directory =  'Data/1_营商数据集转换/AfterProcess'  # 处理后数据集的保存目录\n",
    "\n",
    "# 创建输出目录，如果目录不存在则创建\n",
    "os.makedirs(output_directory, exist_ok=True)\n",
    "\n",
    "# 调用process_all_files函数处理所有文件\n",
    "process_all_files(input_directory, country_list_file, output_directory)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "38ceea4d",
   "metadata": {},
   "source": [
    "1. **import os, pandas, re**：\n",
    "   - `os` 模块用于处理文件和目录路径相关操作。\n",
    "   - `pandas` 是一个流行的数据处理库，用于读取、处理和存储表格数据。\n",
    "   - `re` 模块用于处理正则表达式，用于字符串匹配和提取。\n",
    "\n",
    "2. **`read_country_list(file_path)`**：\n",
    "   - 该函数读取一个包含国家名称对照表的 Excel 文件，并将其转化为一个字典，键是国家的英文简称，值是英文名和中文名的元组。\n",
    "\n",
    "3. **`process_file(file_path, country_dict, output_dir)`**：\n",
    "   - 该函数处理单个 Excel 文件。它将每个国家的指标数据按年展开，并将转换后的数据保存到新文件中。主要步骤包括：\n",
    "     - 读取 Excel 文件。\n",
    "     - 通过正则表达式提取列中的国家简称。\n",
    "     - 将每个国家的每年数据提取并存储。\n",
    "     - 最后保存为新的 Excel 文件。\n",
    "\n",
    "4. **`process_all_files(input_dir, country_list_path, output_dir)`**：\n",
    "   - 该函数遍历指定目录下所有的 Excel 文件，找到符合条件的文件（文件名以`_Indicators.xlsx`结尾），然后调用 `process_file` 逐个处理。\n",
    "\n",
    "5. **`os.makedirs(output_directory, exist_ok=True)`**：\n",
    "   - 该行确保如果输出目录不存在，则自动创建该目录。\n",
    "\n",
    "6. **整个代码的运行流程**：\n",
    "   - 首先，代码读取国家名称对照表。\n",
    "   - 然后，遍历指定输入目录下的所有 Excel 文件。\n",
    "   - 对每个文件进行处理，将其转换为新的格式并保存到指定的输出目录。\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddc062f3",
   "metadata": {},
   "source": [
    "# 批量转换F系列指标文件（F指标数据库不同）\n",
    "\n",
    "1. **函数 `read_country_list(file_path)`：** 读取国家列表<span style=\"color:red\"><b>（global-financial-inclusion GFI数据库）</b></span>及其对应的中文名称，并将其转换为字典格式。\n",
    "2. **函数 `process_file(file_path, country_dict, output_dir)`：** 处理单个Excel文件，进行数据转换并保存结果。如果成功，则输出成功信息；如果失败，则输出失败信息。\n",
    "3. **函数 `process_all_files(input_dir, country_list_path, output_dir)`：** 遍历指定目录下的所有Excel文件，并调用 `process_file` 函数处理每个文件。\n",
    "4. **主程序部分：** 定义输入目录、国家列表文件路径和输出目录，创建输出目录（如果不存在），并调用 `process_all_files` 函数处理所有文件。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "81daf631",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F1_2_Indicators.xlsx 读取并转换为 F1_2_AfterProcess.xlsx 成功\n",
      "F1_Indicators.xlsx 读取并转换为 F1_AfterProcess.xlsx 成功\n",
      "F2_Indicators.xlsx 读取并转换为 F2_AfterProcess.xlsx 成功\n",
      "F3_Indicators.xlsx 读取并转换为 F3_AfterProcess.xlsx 成功\n",
      "F4_Indicators.xlsx 读取并转换为 F4_AfterProcess.xlsx 成功\n"
     ]
    }
   ],
   "source": [
    "import os  # 导入os模块，用于文件和目录操作\n",
    "import pandas as pd  # 导入pandas库用于数据处理\n",
    "import re  # 导入正则表达式模块，用于字符串操作\n",
    "\n",
    "def read_country_list(file_path):\n",
    "    \"\"\"读取国家列表及其对应的中文名称\"\"\"\n",
    "    country_list_df = pd.read_excel(file_path)\n",
    "    return {row['国家名Jc']: (row['国家名En'], row['国家名Ch']) for index, row in country_list_df.iterrows()}\n",
    "\n",
    "def process_file(file_path, country_dict, output_dir):\n",
    "    \"\"\"处理单个Excel文件并保存结果\"\"\"\n",
    "    try:\n",
    "        # 获取文件名和索引名称\n",
    "        file_name = os.path.basename(file_path)\n",
    "        index_name = file_name.split(\"_Indicators\")[0]\n",
    "        \n",
    "        # 读取原始Excel文件\n",
    "        df = pd.read_excel(file_path, sheet_name=\"Data\")\n",
    "        \n",
    "        # 初始化一个空列表，用于存储转换后的数据行\n",
    "        rows = []\n",
    "        \n",
    "        # 遍历原始DataFrame的每一列（除了'Time'列）\n",
    "        for column in df.columns[1:]:\n",
    "            # 使用正则表达式从列名中提取方括号内的国家英文简称\n",
    "            match = re.search(r'\\[([A-Z]+)\\]$', column)\n",
    "            if match:\n",
    "                country_code = match.group(1).strip()\n",
    "                if country_code in country_dict:\n",
    "                    country_en, country_ch = country_dict[country_code]\n",
    "                else:\n",
    "                    continue\n",
    "            else:\n",
    "                continue\n",
    "\n",
    "            # 为当前国家的每一年创建一个数据行\n",
    "            for index, row in df.iterrows():\n",
    "                rows.append({\n",
    "                    '国名En': country_en,\n",
    "                    '国名Ch': country_ch,\n",
    "                    index_name: row[column],\n",
    "                    'Year': row['Time']\n",
    "                })\n",
    "        \n",
    "        # 将收集到的行数据转换为DataFrame\n",
    "        transformed_df = pd.DataFrame(rows, columns=['国名En', '国名Ch', index_name, 'Year'])\n",
    "        \n",
    "        # 构建输出文件路径\n",
    "        output_file_path = os.path.join(output_dir, index_name + '_AfterProcess.xlsx')\n",
    "        \n",
    "        # 将转换后的DataFrame保存到新的Excel文件，不包含索引列\n",
    "        transformed_df.to_excel(output_file_path, index=False)\n",
    "        \n",
    "        # 输出成功信息\n",
    "        print(f\"{file_name} 读取并转换为 {index_name}_AfterProcess.xlsx 成功\")\n",
    "    \n",
    "    except Exception as e:\n",
    "        # 输出失败信息\n",
    "        print(f\"{file_name} 读取并转换为 {index_name}_AfterProcess.xlsx 失败，请检查数据格式！\")\n",
    "        print(f\"错误信息: {e}\")\n",
    "\n",
    "def process_all_files(input_dir, country_list_path, output_dir):\n",
    "    \"\"\"遍历指定目录下的所有Excel文件并处理\"\"\"\n",
    "    # 读取国家列表\n",
    "    country_dict = read_country_list(country_list_path)\n",
    "    \n",
    "    # 遍历输入目录下的所有Excel文件\n",
    "    for file_name in os.listdir(input_dir):\n",
    "        if file_name.endswith(\"_Indicators.xlsx\"):\n",
    "            file_path = os.path.join(input_dir, file_name)\n",
    "            process_file(file_path, country_dict, output_dir)\n",
    "\n",
    "# 定义输入目录、国家列表文件路径和输出目录\n",
    "input_directory = 'Data/1_营商数据集转换/FOrigin'\n",
    "# country_list_file = 'countryList2.xlsx'  ##针对GFI数据库中国家名的映射\n",
    "country_list_file = 'Data/国家名称对照数据表.xlsx'  ##针对WDI数据库的国家名词映射\n",
    "output_directory = 'Data/1_营商数据集转换/FAfterProcess'\n",
    "\n",
    "# 创建输出目录（如果不存在）\n",
    "os.makedirs(output_directory, exist_ok=True)\n",
    "\n",
    "# 处理所有文件\n",
    "process_all_files(input_directory, country_list_file, output_directory)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4b6cf5e",
   "metadata": {},
   "source": [
    "# 批量补齐指标\n",
    "**批量补齐某些指标中缺失国家与缺失年份，保证指标维度的一致性**\n",
    "\n",
    "**代码解释：**\n",
    "1. **读取模板文件**：\n",
    "   - 使用 `pd.read_excel` 读取 `指标补齐示例数据.xlsx`，将其存储在 `template_df` 中。\n",
    "\n",
    "2. **获取指标文件列表**：\n",
    "   - 遍历 `after_process_dir` 目录，筛选出所有以 `_AfterProcess.xlsx` 结尾的文件。\n",
    "\n",
    "3. **创建输出目录**：\n",
    "   - 检查并创建输出目录 `output_dir`。\n",
    "\n",
    "4. **遍历指标文件进行补齐**：\n",
    "   - 对每个指标文件，提取其指标名。\n",
    "   - 读取该文件的内容。\n",
    "   - 将指标名赋值给 `template_df` 中的 `指标名` 列，生成 `template_indicator_df`。\n",
    "   - 将模板数据与指标数据按 `['国名En', '国名Ch', 'Year']` 进行左连接。\n",
    "   - 选择需要的列并重命名，生成 `final_df`。\n",
    "   - 将 `final_df` 保存到 `output_dir` 目录下，文件名格式为 `指标名_AfterProcess2.xlsx`。\n",
    "   - 输出补齐成功或失败的信息，分别以绿色和红色字体显示。\n",
    "\n",
    "5. **统计处理结果**：\n",
    "   - 记录成功处理的文件数 `success_count`。\n",
    "   - 最后输出总文件处理结果，总文件数与成功处理数相同则显示绿色字体提示，否则显示红色字体提示。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "16b5cf9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>D1文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>D2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>D3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>F1_2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>F1文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>F2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>F3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>F4文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>L1文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>L2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>L3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q1文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q2_2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q2_3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q3_2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q3_2（national estimate)文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q3_3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>Q3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>S1文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>S2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>S3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>S4文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>S5文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>T1_2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>T1文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>T2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>T3_2文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style='color:green;'>T3_3文件补齐成功！</span>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<b><span style='color:red;'>~$D1文件补齐失败，请联系管理员！错误信息：Excel file format cannot be determined, you must specify an engine manually.</span></b>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<b><span style='color:red;'>部分文件处理失败，请检查日志。</span></b>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd  # 导入pandas库用于数据处理\n",
    "import os  # 导入os库用于文件和目录操作\n",
    "from IPython.display import display, HTML  # 导入display和HTML用于在Jupyter中显示消息\n",
    "\n",
    "# 指定目录路径\n",
    "after_process_dir = 'Data/1_营商数据集转换/AfterProcess'  # 存储处理后指标数据的目录\n",
    "output_dir = 'Data/1_营商数据集转换/指标补齐后数据'  # 存储补齐后数据的目录\n",
    "template_file = 'Data/1_营商数据集转换/指标补齐示例数据.xlsx'  # 示例数据模板文件路径\n",
    "\n",
    "# 读取补齐示例数据模板\n",
    "template_df = pd.read_excel(template_file)  # 读取Excel文件到DataFrame\n",
    "\n",
    "# 获取指标文件列表\n",
    "indicator_files = [f for f in os.listdir(after_process_dir) if f.endswith('_AfterProcess.xlsx')]  # 获取所有以_AfterProcess.xlsx结尾的文件\n",
    "\n",
    "# 创建输出目录\n",
    "if not os.path.exists(output_dir):  # 如果输出目录不存在\n",
    "    os.makedirs(output_dir)  # 创建输出目录\n",
    "\n",
    "success_count = 0  # 初始化成功处理的文件计数\n",
    "total_files = len(indicator_files)  # 获取总文件数\n",
    "\n",
    "# 遍历每个指标文件进行补齐\n",
    "for file in indicator_files:\n",
    "    try:\n",
    "        # 获取指标名\n",
    "        indicator_name = file.replace('_AfterProcess.xlsx', '')  # 去除文件名中的_AfterProcess.xlsx部分\n",
    "        \n",
    "        # 读取指标数据\n",
    "        file_path = os.path.join(after_process_dir, file)  # 构建完整文件路径\n",
    "        indicator_df = pd.read_excel(file_path)  # 读取Excel文件到DataFrame\n",
    "        \n",
    "        # 选择补齐示例数据中对应指标的行\n",
    "        template_df['指标名'] = indicator_name  # 为示例数据模板添加指标名列\n",
    "        template_indicator_df = template_df.copy()  # 复制示例数据模板\n",
    "        \n",
    "        # 合并补齐数据\n",
    "        merged_df = pd.merge(template_indicator_df, indicator_df, on=['国名En', '国名Ch', 'Year'], how='left')  # 合并数据\n",
    "        \n",
    "        # 选择并重命名列\n",
    "        final_df = merged_df[['国名En', '国名Ch', indicator_name, 'Year']]  # 选择最终需要的列\n",
    "        \n",
    "        # 保存补齐后的数据\n",
    "        output_file = f'{indicator_name}_AfterProcess2.xlsx'  # 构建输出文件名\n",
    "        output_path = os.path.join(output_dir, output_file)  # 构建完整输出路径\n",
    "        final_df.to_excel(output_path, index=False)  # 保存DataFrame到Excel文件\n",
    "        \n",
    "        display(HTML(f\"<span style='color:green;'>{indicator_name}文件补齐成功！</span>\"))  # 显示成功消息\n",
    "        success_count += 1  # 增加成功计数\n",
    "    \n",
    "    except Exception as e:\n",
    "        display(HTML(f\"<b><span style='color:red;'>{indicator_name}文件补齐失败，请联系管理员！错误信息：{e}</span></b>\"))  # 显示错误消息\n",
    "\n",
    "# 显示最终处理结果\n",
    "if success_count == total_files:\n",
    "    display(HTML(f\"<b><span style='color:blue;'>所有{total_files}个文件处理完毕。</span></b>\"))  # 如果所有文件都处理成功，显示成功消息\n",
    "else:\n",
    "    display(HTML(f\"<b><span style='color:red;'>部分文件处理失败，请检查日志。</span></b>\"))  # 如果有文件处理失败，显示部分失败消息\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "766354df",
   "metadata": {},
   "source": [
    "# 批量合并处理后的指标文件示例\n",
    "**代码说明：**\n",
    "\n",
    "1. **函数 `load_and_process_file(file_path)`：** 加载单个Excel文件并将其指标列重命名为文件名，替换所有“..”值为空。\n",
    "2. **函数 `merge_files(input_dir, output_file)`：** 遍历指定目录下的所有Excel文件，调用 `load_and_process_file` 函数处理每个文件，并按“国名En”、“国名Ch”和“Year”列合并数据框，统计合并的文件数量，最终保存合并后的数据框为新的Excel文件，并输出提示信息。\n",
    "3. **主程序部分：** 定义输入目录和输出文件路径，调用 `merge_files` 函数合并所有文件并保存。\n",
    "\n",
    "将 `input_directory` 替换为实际目录路径，然后运行此脚本即可完成合并任务，并输出合并的文件数量。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b55a35f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "所有指标文件已合并保存，共计合并文件数量为：29\n"
     ]
    }
   ],
   "source": [
    "import os  # 导入os模块，用于文件和目录操作\n",
    "import pandas as pd  # 导入pandas库用于数据处理\n",
    "\n",
    "def load_and_process_file(file_path):\n",
    "    \"\"\"加载单个Excel文件并处理数据\"\"\"\n",
    "    df = pd.read_excel(file_path)  # 读取Excel文件内容到数据框df\n",
    "    # 获取文件名（不包含路径和扩展名）\n",
    "    file_name = os.path.basename(file_path).replace('_AfterProcess2.xlsx', '')\n",
    "    # 将所有“..”值替换为空\n",
    "    df.replace('..', '', inplace=True)\n",
    "    # 重命名指标列为文件名\n",
    "    df.rename(columns={df.columns[2]: file_name}, inplace=True)\n",
    "    return df  # 返回处理后的数据框\n",
    "\n",
    "def merge_files(input_dir, output_file):\n",
    "    \"\"\"合并指定目录下的所有Excel文件并保存为一个文件\"\"\"\n",
    "    merged_df = None  # 初始化合并后的数据框为空\n",
    "    file_count = 0  # 初始化文件计数\n",
    "\n",
    "    # 遍历输入目录下的所有Excel文件\n",
    "    for file_name in os.listdir(input_dir):\n",
    "        if file_name.endswith(\"_AfterProcess2.xlsx\"):  # 检查文件名是否以_AfterProcess2.xlsx结尾\n",
    "            file_path = os.path.join(input_dir, file_name)  # 获取文件的完整路径\n",
    "            df = load_and_process_file(file_path)  # 加载并处理文件\n",
    "\n",
    "            if merged_df is None:\n",
    "                merged_df = df  # 如果merged_df为空，则初始化为第一个数据框\n",
    "            else:\n",
    "                # 合并数据框，按列名和年份对齐\n",
    "                merged_df = pd.merge(merged_df, df, on=['国名En', '国名Ch', 'Year'], how='outer')\n",
    "            \n",
    "            file_count += 1  # 增加文件计数\n",
    "\n",
    "    # 保存合并后的数据框为新的Excel文件\n",
    "    merged_df.to_excel(output_file, index=False)  # 将合并后的数据框保存为Excel文件\n",
    "    print(f\"所有指标文件已合并保存，共计合并文件数量为：{file_count}\")  # 打印合并文件的总数\n",
    "\n",
    "# 定义输入目录和输出文件路径\n",
    "# input_directory = '最新营商环境数据/FAfterProcess'  # 替换为实际目录路径\n",
    "# output_file = '最新营商环境数据/FAfterProcess/FAllIndexData.xlsx'  # 定义输出文件路径\n",
    "\n",
    "input_directory = 'Data/1_营商数据集转换/指标补齐后数据'  # 替换为实际目录路径\n",
    "output_file = 'Data/1_营商数据集转换/AllIndexDataTest10-27.xlsx'  # 定义输出文件路径\n",
    "# 合并所有文件并保存\n",
    "merge_files(input_directory, output_file)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae2a1fc1",
   "metadata": {},
   "source": [
    "<a id=\"ref12\"></a>\n",
    "# <span style=\"font-size:24px\">课后作业及思考题</span> \n",
    "<div class=\"alert alert-block alert-info\" style=\"margin-top: 20px; font-size:24px; line-height: 1.5;\">\n",
    "\n",
    "<li><strong style=\"color:red\">数字营商环境指标数据转换及合并</strong>：</li>\n",
    "\n",
    "   - 使用Python编写代码，对实验中下载的其他指标数据进行行列转换，确保所有数据都符合实验要求的格式。\n",
    "   \n",
    "   - 将转换后的所有指标数据与补齐模板进行左连接操作，合并为完整的数字营商环境数据集，并核查合并后的数据完整性。\n",
    "\n",
    "   - 提交转换代码及合并后的数据文件，并总结行列转换过程中遇到的挑战和解决方法。\n",
    "\n",
    "<li><strong style=\"color:red\">补齐缺失数据的策略与方法</strong>：</li>\n",
    "\n",
    "   - 针对实验中的补齐操作，思考在实际评估中如果缺失数据较多，应如何制定合理的补齐策略（如使用均值填补、趋势填补等）？请针对缺失值的不同情况提出三种补齐方法，并说明其适用场景和优劣。\n",
    "   \n",
    "   - 设计一个补齐模板的数据集，针对部分缺失较严重的指标，尝试应用你设计的补齐方法，并对补齐后的数据进行分析与验证。\n",
    "\n",
    "<li><strong style=\"color:red\">数字营商环境数据的一致性与匹配性检验</strong>：</li>\n",
    "\n",
    "   - 从合并后的数字营商环境数据集中，随机选择3个国家和3个年份的数据，手动与原始数据进行对比，检查数据的一致性。\n",
    "   \n",
    "   - 使用Python编写一个程序，对合并后的数据进行系统性一致性检查，找出可能存在的误差或遗漏，并对检查结果进行总结与分析。\n",
    "\n",
    "</div>\n",
    "\n",
    "<div class=\"alert alert-block alert-info\" style=\"margin-top: 20px; font-size:24px; line-height: 1.5;\">\n",
    "\n",
    "<li><strong style=\"color:red\">思考题1: 补齐缺失数据对评估结果的影响</strong>：</li>\n",
    "\n",
    "   - 在评估数字营商环境时，数据的完整性和一致性至关重要。请思考，如果某些国家的关键指标数据严重缺失且无法合理补齐，会对整体评估结果产生什么影响？\n",
    "   \n",
    "   - 请讨论如何在报告中解释这些数据缺失对结果的影响，并建议如何在实际分析中最小化这些影响。\n",
    "\n",
    "<li><strong style=\"color:red\">思考题2: 指标权重设定的敏感性分析</strong>：</li>\n",
    "\n",
    "   - 在数字营商环境评估中，不同指标的权重设定会直接影响最终的评估结果。请思考如何对指标权重进行设置，以确保评估结果的稳定性和公正性。\n",
    "   \n",
    "  \n",
    "\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69d025bd",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
